﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Recreation
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Recreation
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetRecreation(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  re.RecreationKey,
        re.ApplicantKey,
        hbc.DisplayText AS 'HobbyCategoryDesc',
        hbd.DisplayText AS 'HobbyDescriptionDesc',
        re.HobbyOthers,
        re.HobbyCategory,
        re.HobbyDescription
FROM    SRM_ApplicantRecreation_Tbl re
LEFT JOIN
        SAP_Code hbc 
        ON  hbc.CodeTypeId = 'HBC' 
        AND hbc.Id = re.HobbyCategory
LEFT JOIN
        SAP_Code hbd 
        ON  hbd.CodeTypeId = 'HBD' 
        AND hbd.ParentId1 = re.HobbyCategory
        AND hbd.Id = re.HobbyDescription
WHERE   re.ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetRecreation(string applicantKey)



        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertRecreation(string applicantKey,
            string hobbyCategory, string hobbyDescription, string hobbyOthers)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT SRM_ApplicantRecreation_Tbl (ApplicantKey, HobbyCategory, HobbyDescription, HobbyOthers) VALUES (@ApplicantKey, @HobbyCategory, @HobbyDescription, @HobbyOthers);";
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@HobbyCategory", Help.GetValueOrDBNull(hobbyCategory));
                cmd.Parameters.AddWithValue("@HobbyDescription", Help.GetValueOrDBNull(hobbyDescription));
                cmd.Parameters.AddWithValue("@HobbyOthers", Help.GetValueOrDBNull(hobbyOthers));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteRecreation(string recreationKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantRecreation_Tbl WHERE RecreationKey = @RecreationKey;";
                cmd.Parameters.AddWithValue("@RecreationKey", recreationKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateRecreation(string recreationKey,
            string hobbyCategory, string hobbyDescription, string hobbyOthers)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantRecreation_Tbl 
SET     HobbyCategory = @HobbyCategory, 
        HobbyDescription = @HobbyDescription,
        HobbyOthers = @HobbyOthers
WHERE   RecreationKey = @RecreationKey;";

                cmd.Parameters.AddWithValue("@RecreationKey", recreationKey);
                cmd.Parameters.AddWithValue("@HobbyCategory", Help.GetValueOrDBNull(hobbyCategory));
                cmd.Parameters.AddWithValue("@HobbyDescription", Help.GetValueOrDBNull(hobbyDescription));
                cmd.Parameters.AddWithValue("@HobbyOthers", Help.GetValueOrDBNull(hobbyOthers));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateRecreation(string RecreationKey,

    }
}